﻿Imports System.Data.SqlClient

Public Class clsCapacitacion
    Private mintidCapacitacion As Integer
    Private mintidEmpleado As Integer
    Private mstrTarjeton As String
    Private mstrTipo As String
    Private mstrModulo As String
    Private mdatFechaExpedido As Date
    Private mdatFechaExpira As Date
    Private mdatFechaRecive As Date
    Private mstrStatus As String
    Public Property idCapacitacion() As Integer
        Get
            Return mintidCapacitacion
        End Get
        Set(ByVal value As Integer)
            mintidCapacitacion = value
        End Set
    End Property
    Public Property idEpleado() As Integer
        Get
            Return mintidEmpleado
        End Get
        Set(ByVal value As Integer)
            mintidEmpleado = value
        End Set
    End Property
    Public Property Tarjeton() As String
        Get
            Return mstrTarjeton
        End Get
        Set(ByVal value As String)
            mstrTarjeton = value
        End Set
    End Property
    Public Property Tipo() As String
        Get
            Return mstrTipo
        End Get
        Set(ByVal value As String)
            mstrTipo = value
        End Set
    End Property
    Public Property Modulo() As String
        Get
            Return mstrModulo
        End Get
        Set(ByVal value As String)
            mstrModulo = value
        End Set
    End Property
    Public Property FechaeExpedido() As Date
        Get
            Return mdatFechaExpedido
        End Get
        Set(ByVal value As Date)
            mdatFechaExpedido = value
        End Set
    End Property
    Public Property FechaExpira() As Date
        Get
            Return mdatFechaExpira
        End Get
        Set(ByVal value As Date)
            mdatFechaExpira = value
        End Set
    End Property
    Public Property FechaRecive() As Date
        Get
            Return mdatFechaRecive
        End Get
        Set(ByVal value As Date)
            mdatFechaRecive = value
        End Set
    End Property
    Public Property Status() As String
        Get
            Return mstrStatus
        End Get
        Set(ByVal value As String)
            mstrStatus = value
        End Set
    End Property

    Public Sub New()
        mintidCapacitacion = 0
        mintidEmpleado = 0
        mstrTarjeton = ""
        mstrTipo = ""
        mstrModulo = ""
        mdatFechaExpedido = Now()
        mdatFechaExpira = Now()
        mdatFechaRecive = Now()
        mstrStatus = ""

    End Sub
    Public Sub Guardar()

        If idCapacitacion = 0 Then
            Exit Sub
        End If

        Dim intRetVal As Integer

        intRetVal = BuscarIntDatoCitra("Capacitacion", "idCapacitacion", "idCapacitacion=" & idCapacitacion)

        If intRetVal = 0 Then
            Insertar()
        Else
            Actualizar2()

        End If

    End Sub
    Public Sub Borrar()
        Call SetSqlCitra("Delete From Capacitacion Where idCapacitacion = " & idCapacitacion)

    End Sub
    Public Sub Buscar()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From Capacitacion " & _
        " WHERE idCapacitacion=" & idCapacitacion

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        If rdBuscar.Read() Then
            mintidCapacitacion = rdBuscar("idcapacitacion")
            mintidEmpleado = rdBuscar("idempleado")
            mstrTarjeton = rdBuscar("tarjeton").ToString.Trim
            mstrTipo = rdBuscar("tipo").ToString.Trim
            mstrModulo = rdBuscar("modulo").ToString.Trim
            mdatFechaExpedido = rdBuscar("fechaexpedido")
            mdatFechaExpira = rdBuscar("fechaexpira")
            mdatFechaRecive = rdBuscar("fecharecibe")
            mstrStatus = rdBuscar("status").ToString.Trim

        Else
            mintidCapacitacion = 0
            mintidEmpleado = 0
            mstrTarjeton = ""
            mstrTipo = ""
            mstrModulo = ""
            mdatFechaExpedido = Now()
            mdatFechaExpira = Now()
            mdatFechaRecive = Now()
            mstrStatus = ""
        End If

        rdBuscar.Close()
        cnConn.Close()


    End Sub
    Public Sub Insertar()
        'Actualizar()

        Dim sql As String = "Insert Into Capacitacion (" & _
                           "IdCapacitacion," & _
                           "IdEmpleado," & _
                           "Tarjeton," & _
                           "Tipo," & _
                           "Modulo," & _
                           "FechaExpedicion," & _
                           "FechaExpira," & _
                           "FechaRecibe," & _
                           "Status)" & _
                           "VALUES (" & _
                           "@IdCapacitacion," & _
                           "@IdEmpleado," & _
                           "substring(@Tarjeton,1,10)," & _
                           "substring(@Tipo,1,10)," & _
                           "substring(@Modulo,1,15)," & _
                           "@FechaExpedicion," & _
                           "@FechaExpira," & _
                           "@FechaRecibe," & _
                           "substring(@Status,1,10))"

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim Command As New SqlCommand(sql, cnConn)

        Command.Parameters.AddWithValue("IdCapacitacion", mintidCapacitacion)
        Command.Parameters.AddWithValue("IdEmpleado", mintidEmpleado)
        Command.Parameters.AddWithValue("Tarjeton", mstrTarjeton)
        Command.Parameters.AddWithValue("Tipo", mstrTipo)
        Command.Parameters.AddWithValue("Modulo", mstrModulo)
        Command.Parameters.AddWithValue("FechaExpedicion", mdatFechaExpedido)
        Command.Parameters.AddWithValue("FechaExpira", mdatFechaExpira)
        Command.Parameters.AddWithValue("FechaRecibe", mdatFechaRecive)
        Command.Parameters.AddWithValue("Status", mstrStatus)

        cnConn.Open()
        Dim RowsAffected As Integer
        Try
            RowsAffected = Command.ExecuteNonQuery
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        End Try

        If RowsAffected > 0 Then
            cnConn.Close()
        Else
            MsgBox("No fue posible insertar el registro ", MsgBoxStyle.Exclamation, "Aviso")
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End If
      
    End Sub
    Public Sub Actualizar()
        Dim strSqlQry As String
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        strSqlQry = "Update Capacitacion Set " & _
                            "Status=@Status " & _
                            " where IdEmpleado=@IdEmpleado"


        Dim Command As New SqlCommand(strSqlQry, cnConn)
        Command.Parameters.AddWithValue("IdEmpleado", mintidEmpleado)
        Command.Parameters.AddWithValue("Status", "BAJA")
        cnConn.Open()

        Dim RowsAffected As Integer = Command.ExecuteNonQuery

        If RowsAffected > 0 Then
            cnConn.Close()
        End If



    End Sub
    Public Sub Actualizar2()
        Dim strSqlQry As String
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = NominasConnection

        strSqlQry = "Update Capacitacion Set " & _
                    " Tarjeton=@Tarjeton, " & _
                    " Modulo=substring(@Modulo,1,15), " & _
                    " FechaExpedicion=@FechaExpedicion, " & _
                    " FechaExpira=@FechaExpira, " & _
                    " FechaRecibe=@FechaRecibe " & _
                    " where IdCapacitacion=@IdCapacitacion"


        Dim Command As New SqlCommand(strSqlQry, cnConn)
        Command.Parameters.AddWithValue("IdCapacitacion", mintidCapacitacion)
        Command.Parameters.AddWithValue("IdEmpleado", mintidEmpleado)
        Command.Parameters.AddWithValue("Tarjeton", mstrTarjeton)
        Command.Parameters.AddWithValue("Tipo", mstrTipo)
        Command.Parameters.AddWithValue("Modulo", mstrModulo)
        Command.Parameters.AddWithValue("FechaExpedicion", mdatFechaExpedido)
        Command.Parameters.AddWithValue("FechaExpira", mdatFechaExpira)
        Command.Parameters.AddWithValue("FechaRecibe", mdatFechaRecive)
        Command.Parameters.AddWithValue("Status", mstrStatus)


        cnConn.Open()

        Dim RowsAffected As Integer = Command.ExecuteNonQuery

        If RowsAffected > 0 Then
            cnConn.Close()
        Else
            MsgBox("No fue posible actualizar el registro", MsgBoxStyle.Exclamation, "Aviso")
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End If
    End Sub
End Class
